/*
 * EntityHelper.java
 *
 * Created on March 31, 2010, 4:50 PM
 *
 */

package com.absisgroup.nsc.utilities;

import java.lang.reflect.*;
import java.sql.*;
import java.util.List;
import java.util.StringTokenizer;
/**
 *
 * @author Hue
 */
public class EntityManager {

    public static boolean store(final Connection con, final Entity entity) throws Exception {
        final Long entityPK = getEntityPK(entity);
        if(entity.isRemoved()) {
            return delete(con, entity, entityPK);
        }
        else if(entityPK == null) {
            return insert(con, entity);
        }
        else {
            return update(con, entity, entityPK);
        }
    }

    private static String arrayToColumns(final List arrCols) {
        final StringBuffer columns = new StringBuffer();
        for(int i=0; i<arrCols.size(); i++) {
            columns.append(i == 0 ? "" : ",");
            columns.append(arrCols.get(i));
        }
        return columns.toString();
    }

    private static void initObject(final Object obj) throws Exception {
        final Method[] methods = obj.getClass().getDeclaredMethods();
        final int iLength = methods.length;
        for(int i=0; i<iLength; i++) {
            final Method m = methods[i];
            if(m.getName().startsWith("set")) {
                m.invoke(obj, new Object[] { null });
            }
        }
    }

    public static Entity findByPrimaryKey(final Connection con, final Class clazz, final long recordPK) throws Exception {
        final Entity entity = (Entity) clazz.newInstance();
        load(con, entity, recordPK);

        return (getEntityPK(entity) == null ? null : entity);
    }
    
    private static void load(final Connection con, final Entity entity, final long recordPK) throws Exception {
        initObject(entity);
        final MappingInfo mappingInfo = entity.mappingInfo();
        final List arrCols = entity.getColumns();
        //Init SELECT statement
        final StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append(arrayToColumns(arrCols));
        sb.append(" FROM ");
        sb.append(mappingInfo.getTableName());
        sb.append(" WHERE ");
        sb.append(mappingInfo.getPrimaryKey());
        sb.append(" = ?");
        //Execute Query
        final PreparedStatement pStmt = con.prepareStatement(sb.toString());
        pStmt.setLong(1, recordPK);
        final ResultSet rsOwn = pStmt.executeQuery();
        final DBResultSet rs = new DBResultSet(rsOwn);
        if(rs.next()) {
            setEntityPK(entity, new Long(recordPK));
            mapValues(rs, entity, arrCols);
        }
        else {
            setEntityPK(entity, null);
        }
        entity.resetColumns();

        pStmt.close();
    }

    public static long getCount(final Connection con, final String strQuery) throws Exception {
        return getCount(con, strQuery, null);
    }

    public static long getCount(final Connection con, final String strQuery, final List params) throws Exception {
        final PreparedStatement pStmt = con.prepareStatement("select count(*) from (" + strQuery + ")");
        int iIndex = 1;
        for(int i=0; i<params.size(); i++) {
            pStmt.setObject(iIndex++, params.get(i));
        }
        final ResultSet rs = pStmt.executeQuery();
        long lCount = 0;
        if(rs.next()) {
            lCount = rs.getLong(1);
        }
        pStmt.close();

        return lCount;
    }

    public static List findByQuery(final Connection con, final Class clazz, final String strQuery) throws Exception {
        return findByQuery(con, clazz, strQuery, null);
    }

    public static List findByQuery(final Connection con, final Class clazz, final String strQuery, final List params) throws Exception {
        final Object obj = clazz.newInstance();
        boolean bIsEntity = false;
        List arrCols = null;
        if(obj instanceof Entity) {
            initObject(obj);
            arrCols = ((Entity) obj).getColumns();
            final MappingInfo mappingInfo = ((Entity)obj).mappingInfo();
            arrCols.add(mappingInfo.getPrimaryKey());
            bIsEntity = true;
        }
        else if(obj instanceof ICustomEntity) {
            arrCols = stringToArray(((ICustomEntity) obj).getColumns(), ",");
        }
        else {
            throw new Exception(obj.getClass() + " is not an instanceof Entity or ICustomEntity.");
        }
        final List list = new java.util.ArrayList();
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        if(params != null) {
            int iIndex = 1;
            for(int i=0; i<params.size(); i++) {
                pStmt.setObject(iIndex++, params.get(i));
            }
        }
        final ResultSet rsOwn = pStmt.executeQuery();
        final DBResultSet rs = new DBResultSet(rsOwn);
        while(rs.next()) {
            final Object entity = clazz.newInstance();
            mapValues(rs, entity, arrCols);
            if(bIsEntity) {
                ((Entity) entity).resetColumns();
            }
            list.add(entity);
        }
        pStmt.close();

        return list;
    }

    private static void mapValues(final DBResultSet rs, final Object entity, final List arrCols) throws Exception {
        for(int i=0; i<arrCols.size(); i++) {
            final String property = columnToProperty((String) arrCols.get(i));
            final Class dataType = getReturnType(entity, "get" + property);
            final String rsMethod = "get" + dataType.getName().substring(dataType.getName().lastIndexOf('.') + 1);
            final Object value = invoke(rs, rsMethod, new Class[] {String.class}, new Object[] {arrCols.get(i)});
            invoke(entity, "set" + property, new Class[] {dataType}, new Object[] {value});
        }
    }

    public static void showAllValues(final Object obj) throws Exception {
        if(obj == null) {
            System.out.println("Object is NULL");
            return;
        }
        final Method[] methods = obj.getClass().getMethods();
        final int iLength = methods.length;
        for(int i=0; i<iLength; i++) {
            if(methods[i].getName().startsWith("get")) {
                System.out.println(methods[i].getName() + ":" + methods[i].invoke(obj, new Object[] {}));
            }
        }
    }

    private static void setEntityPK(final Entity entity, final Long entityPK) throws Exception {
        final MappingInfo mappingInfo = entity.mappingInfo();
        invoke(entity, "set" + columnToProperty(mappingInfo.getPrimaryKey()), new Class[] {Long.class}, new Object[] {entityPK});
    }

    private static Long getEntityPK(final Entity entity) throws Exception {
        final MappingInfo mappingInfo = entity.mappingInfo();
        final Object value = invoke(entity, "get" + columnToProperty(mappingInfo.getPrimaryKey()), new Class[] {}, new Object[] {});

        return (Long) value;
    }

    private static boolean update(final Connection con, final Entity entity, final Long entityPK) throws Exception {
        final List arrCols = entity.getColumns();
        if(arrCols.isEmpty()) {
            return true;
        }
        final MappingInfo mappingInfo = entity.mappingInfo();
        final StringBuffer sb = new StringBuffer();
        sb.append("UPDATE ");
        sb.append(mappingInfo.getTableName());
        sb.append(" SET ");
        for(int i=0; i<arrCols.size(); i++) {
            if(i==0) {
                sb.append(arrCols.get(i)).append(" = ?");
            }
            else {
                sb.append(",").append(arrCols.get(i)).append(" = ?");
            }
        }
        sb.append(" WHERE ");
        sb.append(mappingInfo.getPrimaryKey());
        sb.append(" = ?");
        final PreparedStatement pStmt = con.prepareStatement(sb.toString());
        int iIndex = 1;
        for(int i=0; i<arrCols.size(); i++) {
            final String method = "get" + columnToProperty((String) arrCols.get(i));
            final Class dataType = getReturnType(entity, method);
            final Object value = invoke(entity, method, new Class[] {}, new Object[] {});
            final String stmtMethod = "set" + dataType.getName().substring(dataType.getName().lastIndexOf('.') + 1);
            invoke(pStmt, stmtMethod, new Class[] {int.class, dataType}, new Object[] {new Integer(iIndex++), value});
        }
        pStmt.setLong(iIndex++, entityPK.longValue());
        pStmt.executeUpdate();
        pStmt.close();
        entity.resetColumns();

        return true;
    }

    private static boolean insert(final Connection con, final Entity entity) throws Exception {
        return insert(con, entity, "SQ_" + entity.mappingInfo().getPrimaryKey());
    }

    public static boolean insert(final Connection con, final Entity entity, final String sequenceName) throws Exception {
        final PreparedStatement pStmt = con.prepareStatement("SELECT " + sequenceName + ".nextval from dual");
        final ResultSet rs = pStmt.executeQuery();
        long entityPK = -1;
        if(rs.next()) {
            entityPK = rs.getLong(1);
        }
        pStmt.close();

        return insert(con, entity, entityPK);
    }

    public static boolean insert(final Connection con, final Entity entity, final long entityPK) throws Exception {
        final List arrCols = entity.getColumns();
        if(arrCols.isEmpty()) {
            return true;
        }
        //
        final MappingInfo mappingInfo = entity.mappingInfo();
        final StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ");
        sb.append(mappingInfo.getTableName());
        sb.append('(');
        sb.append(mappingInfo.getPrimaryKey());
        sb.append(',');
        sb.append(arrayToColumns(arrCols));
        sb.append(") VALUES(");
        sb.append('?');
        for(int i=0; i<arrCols.size(); i++) {
            sb.append(",?");
        }
        sb.append(')');
        //
        final PreparedStatement pStmt = con.prepareStatement(sb.toString());
        int iIndex = 1;
        pStmt.setLong(iIndex++, entityPK);
        for(int i=0; i<arrCols.size(); i++) {
            final String method = "get" + columnToProperty((String) arrCols.get(i));
            final Class dataType = getReturnType(entity, method);
            final Object value = invoke(entity, method, new Class[] {}, new Object[] {});
            final String stmtMethod = "set" + dataType.getName().substring(dataType.getName().lastIndexOf('.') + 1);
            invoke(pStmt, stmtMethod, new Class[] {int.class, dataType}, new Object[] {new Integer(iIndex++), value});
        }
        pStmt.executeUpdate();
        pStmt.close();

        load(con, entity, entityPK);

        return true;
    }

    private static boolean delete(final Connection con, final Entity entity, final Long entityPK) throws Exception {
        if(entityPK == null) {
            return false;
        }
        final MappingInfo mappingInfo = entity.mappingInfo();
        final StringBuffer sb = new StringBuffer();
        sb.append("DELETE FROM ");
        sb.append(mappingInfo.getTableName());
        sb.append(" WHERE ");
        sb.append(mappingInfo.getPrimaryKey());
        sb.append(" = ?");
        final PreparedStatement pStmt = con.prepareStatement(sb.toString());
        pStmt.setLong(1, entityPK.longValue());
        pStmt.executeUpdate();
        pStmt.close();

        return true;
    }

    private static Class getReturnType(final Object obj, final String methodName) throws Exception {
        final Method method = obj.getClass().getMethod(methodName, new Class[]{});
        return method.getReturnType();
    }

    private static Object invoke(final Object obj, final String methodName, final Class[] argType, final Object[] argValue) throws Exception {
        final Method method = obj.getClass().getMethod(methodName, argType);
        return method.invoke(obj, argValue);
    }

    private static List stringToArray(final String str, final String delim) {
        final StringTokenizer tokens = new StringTokenizer(str, delim);
        final List arr = new java.util.ArrayList();
        while(tokens.hasMoreTokens()) {
            arr.add(tokens.nextToken());
        }
        return arr;
    }

    private static String columnToProperty(final String column) {
        String property = column.toLowerCase();
        for(int iIndex = 0; (iIndex = property.indexOf("_", iIndex)) != -1;) {
            final String strHeader = property.substring(0, iIndex);
            final String strContent = property.substring(iIndex + 1, iIndex + 2);
            final String strFooter = property.substring(iIndex + 2);
            property = strHeader + strContent.toUpperCase() + strFooter;
        }
        return property.substring(0, 1).toUpperCase() + property.substring(1);
    }
}